﻿/*********************************************************
 * 开发人员：小兵
 * 创建时间：2012-10-8 17:32:19
 * 描述说明：
 * 
 * 更改历史：
 * 
 * *******************************************************/
namespace Mozlite.Data
{
    using System;
    using System.Web;
    using System.Data;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using Mozlite.Data.Components;
    using System.Xml;
    using System.IO;
    using System.Text.RegularExpressions;

    /// <summary>
    /// 数据库辅助方法类。
    /// </summary>
    public abstract class SqlHelper<TParameter> : ISqlHelper where TParameter : IDataParameter
    {
        private readonly string connectionString;
        /// <summary>
        /// 获取连接字符串。
        /// </summary>
        public string ConnectionString
        {
            get { return connectionString; }
        }

        /// <summary>
        /// 安装工厂实例对象。
        /// </summary>
        protected IInstallerFactory installer;
        /// <summary>
        /// 安装工厂实例对象。
        /// </summary>
        public IInstallerFactory Installer
        {
            get { return installer; }
        }

        /// <summary>
        /// 初始化类<see cref="SqlHelper&lt;TParameter&gt;"/>。
        /// </summary>
        /// <param name="connectionString">链接字符串。</param>
        public SqlHelper(string connectionString)
            : this(connectionString, null)
        { }

        /// <summary>
        /// 初始化类<see cref="SqlHelper&lt;TParameter&gt;"/>。
        /// </summary>
        /// <param name="connectionString">链接字符串。</param>
        /// <param name="factory">安装工厂实例对象，如果为<c>null</c>，则使用默认工厂实例。</param>
        public SqlHelper(string connectionString, IInstallerFactory factory)
        {
            if(connectionString == null)
                throw new ArgumentNullException("connectionString");
            if(factory == null)
                factory = new DefaultInstallerFactory<SqlHelper<TParameter>>(this);

            this.connectionString = connectionString;
            this.installer = factory;
        }

        /// <summary>
        /// 垃圾回收<see cref="SqlHelper&lt;TParameter&gt;"/>。
        /// </summary>
        ~SqlHelper()
        {
            Dispose();
        }

        /// <summary>
        /// 转换SQL语句。
        /// </summary>
        /// <param name="commandText">SQL语句。</param>
        /// <returns>默认返回当前SQL语句，子类进行重新转换。</returns>
        protected virtual string ConvertCommand(string commandText)
        {
            if(commandText == null)
                throw new ArgumentNullException("commandText");
            return commandText;
        }

        /// <summary>
        /// 参数转换。
        /// </summary>
        /// <param name="parameters">将参数转换为<see cref="ISqlParameter"/>。</param>
        /// <returns>返回转换后的参数数组。</returns>
        protected virtual TParameter[] ConvertParameters(IParameter[] parameters)
        {
            if(parameters == null)
                return null;

            TParameter[] newParams = new TParameter[parameters.Length];
            for(int index = 0; index < parameters.Length; index++)
            {
                ISqlParameter<TParameter> parameter = parameters[index] as ISqlParameter<TParameter>;
                if(parameter == null)
                    throw new ArgumentException("Element {0} of parameters has the wrong type. Expected: ISqlParameter<{1}>. Received: {2}.".FormatWith(
                                       index,
                                       typeof(TParameter).Name,
                                       (parameter == null ? "null" : parameter.GetType().Name)),
                        "parameters");
                newParams[index] = parameter.RawParameter;
            }
            return newParams;
        }

        /// <summary>
        /// 转换返回的单值类型。
        /// </summary>
        /// <typeparam name="T">返回值得类型。</typeparam>
        /// <param name="scalar">执行ExecuteScalar方法后返回的值。</param>
        /// <returns>返回转换后的值。</returns>
        protected virtual T ConvertScalar<T>(object scalar)
        {
            if(scalar == null || (scalar == DBNull.Value && !typeof(T).IsAssignableFrom(typeof(DBNull))))
                return default(T);
            if (typeof(T) == typeof(bool))
                return (T)((object)(scalar.ToString() == "1"));
            return scalar.To<T>();
        }

        /// <summary>
        /// 过滤危险字符。
        /// </summary>
        /// <param name="value">用于过滤的字符串。</param>
        /// <returns>返回过滤替换后的字符串。</returns>
        public virtual string EscapeString(string value)
        {
            return string.IsNullOrEmpty(value) ? string.Empty : value.Replace("'", "''");
        }

        /// <summary>
        /// 创建参数对象。
        /// </summary>
        /// <param name="parameterName">参数名称。</param>
        /// <param name="value">参数值。</param>
        /// <returns>返回参数对象实例。</returns>
        public abstract IParameter CreateParameter(string parameterName, object value);

        /// <summary>
        /// 启动事务处理对象实例。
        /// </summary>
        /// <return>当前事务处理实例对象。</return>
        public abstract IDbTransaction BeginTransaction();

        #region common
        /// <summary>
        /// 执行单值返回方法。
        /// </summary>
        /// <typeparam name="T">返回类型。</typeparam>
        /// <param name="commandText">SQL语句。</param>
        /// <param name="parameters">参数数组。</param>
        /// <returns>返回执行后的结果。</returns>
        public T ExecuteScalar<T>(string commandText, params IParameter[] parameters)
        {
            return ExecuteScalar<T>(CommandType.Text, commandText, parameters);
        }

        /// <summary>
        /// 执行不返回值得查询语句。
        /// </summary>
        /// <param name="commandText">SQL语句。</param>
        /// <param name="parameters">参数数组。</param>
        /// <returns>
        /// 返回影响的行数。
        /// </returns>
        public int ExecuteNonQuery(string commandText, params IParameter[] parameters)
        {
            return ExecuteNonQuery(CommandType.Text, commandText, parameters);
        }

        /// <summary>
        /// 执行查询语句。
        /// </summary>
        /// <param name="commandText">SQL语句。</param>
        /// <param name="parameters">参数数组。</param>
        /// <returns>
        /// 返回数据库读取对象实例。
        /// </returns>
        public IDataReader ExecuteReader(string commandText, params IParameter[] parameters)
        {
            return ExecuteReader(CommandType.Text, commandText, parameters);
        }

        /// <summary>
        /// 执行并返回一个XML读取对象实例。
        /// </summary>
        /// <param name="commandText">SQL语句。</param>
        /// <param name="parameters">参数数组。</param>
        /// <returns>
        /// 返回XML读取对象实例。
        /// </returns>
        public XmlReader ExecuteXmlReader(string commandText, params IParameter[] parameters)
        {
            return ExecuteXmlReader(CommandType.Text, commandText, parameters);
        }

        /// <summary>
        /// 执行命令语句，并返回影响行数。
        /// </summary>
        /// <param name="commandType">命令类型。</param>
        /// <param name="commandText">SQL语句。</param>
        /// <param name="parameters">参数数组。</param>
        /// <returns>返回影响的行数。</returns>
        public int ExecuteNonQuery(CommandType commandType, string commandText, params IParameter[] parameters)
        {
            return ExecuteNonQuery(null, CommandType.Text, commandText, parameters);
        }

        /// <summary>
        /// 执行命令语句，并返回<see cref="T:System.Data.IDataReader"/>实例对象。
        /// </summary>
        /// <param name="commandType">命令类型。</param>
        /// <param name="commandText">SQL语句。</param>
        /// <param name="parameters">参数数组。</param>
        /// <returns>返回<see cref="T:System.Data.IDataReader"/>实例对象。</returns>
        public IDataReader ExecuteReader(CommandType commandType, string commandText, params IParameter[] parameters)
        {
            return ExecuteReader(null, CommandType.Text, commandText, parameters);
        }

        /// <summary>
        /// 执行命令语句，并返回结果实例对象。
        /// </summary>
        /// <typeparam name="T">返回结果类型。</typeparam>
        /// <param name="commandType">命令类型。</param>
        /// <param name="commandText">SQL语句。</param>
        /// <param name="parameters">参数数组。</param>
        /// <returns>返回结果实例对象。</returns>
        public T ExecuteScalar<T>(CommandType commandType, string commandText, params IParameter[] parameters)
        {
            return ExecuteScalar<T>(null, CommandType.Text, commandText, parameters);
        }

        /// <summary>
        /// 执行命令语句，并返回<see cref="T:System.Xml.XmlReader"/>实例对象。
        /// </summary>
        /// <param name="commandType">命令类型。</param>
        /// <param name="commandText">SQL语句。</param>
        /// <param name="parameters">参数数组。</param>
        /// <returns>返回<see cref="T:System.Xml.XmlReader"/>实例对象。</returns>
        public XmlReader ExecuteXmlReader(CommandType commandType, string commandText, params IParameter[] parameters)
        {
            return ExecuteXmlReader(null, CommandType.Text, commandText, parameters);
        }
        #endregion

        #region transactions
        /// <summary>
        /// 执行命令语句，并返回影响行数。
        /// </summary>
        /// <param name="transaction">事务处理实例对象。</param>
        /// <param name="commandText">SQL语句。</param>
        /// <param name="parameters">参数数组。</param>
        /// <returns>返回影响的行数。</returns>
        public int ExecuteNonQuery(IDbTransaction transaction, string commandText, params IParameter[] parameters)
        {
            return ExecuteNonQuery(transaction, CommandType.Text, commandText, parameters);
        }

        /// <summary>
        /// 执行命令语句，并返回<see cref="T:System.Data.IDataReader"/>实例对象。
        /// </summary>
        /// <param name="transaction">事务处理实例对象。</param>
        /// <param name="commandText">SQL语句。</param>
        /// <param name="parameters">参数数组。</param>
        /// <returns>返回<see cref="T:System.Data.IDataReader"/>实例对象。</returns>
        public IDataReader ExecuteReader(IDbTransaction transaction, string commandText, params IParameter[] parameters)
        {
            return ExecuteReader(transaction, CommandType.Text, commandText, parameters);
        }

        /// <summary>
        /// 执行命令语句，并返回结果实例对象。
        /// </summary>
        /// <typeparam name="T">返回结果类型。</typeparam>
        /// <param name="transaction">事务处理实例对象。</param>
        /// <param name="commandText">SQL语句。</param>
        /// <param name="parameters">参数数组。</param>
        /// <returns>返回结果实例对象。</returns>
        public T ExecuteScalar<T>(IDbTransaction transaction, string commandText, params IParameter[] parameters)
        {
            return ExecuteScalar<T>(transaction, CommandType.Text, commandText, parameters);
        }

        /// <summary>
        /// 执行命令语句，并返回<see cref="T:System.Xml.XmlReader"/>实例对象。
        /// </summary>
        /// <param name="transaction">事务处理实例对象。</param>
        /// <param name="commandText">SQL语句。</param>
        /// <param name="parameters">参数数组。</param>
        /// <returns>返回<see cref="T:System.Xml.XmlReader"/>实例对象。</returns>
        public XmlReader ExecuteXmlReader(IDbTransaction transaction, string commandText, params IParameter[] parameters) {
            return ExecuteXmlReader(transaction, CommandType.Text, commandText, parameters);
        }

        /// <summary>
        /// 执行命令语句，并返回影响行数。
        /// </summary>
        /// <param name="transaction">事务处理实例对象。</param>
        /// <param name="commandType">命令类型。</param>
        /// <param name="commandText">SQL语句。</param>
        /// <param name="parameters">参数数组。</param>
        /// <returns>返回影响的行数。</returns>
        public int ExecuteNonQuery(IDbTransaction transaction, CommandType commandType, string commandText, params IParameter[] parameters)
        {
            string commandConverted = ConvertCommand(commandText);
            TParameter[] parametersConverted = ConvertParameters(parameters);
            return ExecuteNonQuery(transaction, commandType, commandConverted.TrimToLine(), parametersConverted);
        }

        /// <summary>
        /// 执行命令语句，并返回<see cref="T:System.Data.IDataReader"/>实例对象。
        /// </summary>
        /// <param name="transaction">事务处理实例对象。</param>
        /// <param name="commandType">命令类型。</param>
        /// <param name="commandText">SQL语句。</param>
        /// <param name="parameters">参数数组。</param>
        /// <returns>返回<see cref="T:System.Data.IDataReader"/>实例对象。</returns>
        public IDataReader ExecuteReader(IDbTransaction transaction, CommandType commandType, string commandText, params IParameter[] parameters)
        {
            string commandConverted = ConvertCommand(commandText);
            TParameter[] parametersConverted = ConvertParameters(parameters);
            return ExecuteReader(transaction, commandType, commandConverted.TrimToLine(), parametersConverted);
        }

        /// <summary>
        /// 执行命令语句，并返回结果实例对象。
        /// </summary>
        /// <typeparam name="T">返回结果类型。</typeparam>
        /// <param name="transaction">事务处理实例对象。</param>
        /// <param name="commandType">命令类型。</param>
        /// <param name="commandText">SQL语句。</param>
        /// <param name="parameters">参数数组。</param>
        /// <returns>返回结果实例对象。</returns>
        public T ExecuteScalar<T>(IDbTransaction transaction, CommandType commandType, string commandText, params IParameter[] parameters)
        {
            string commandConverted = ConvertCommand(commandText);
            TParameter[] parametersConverted = ConvertParameters(parameters);
            return ConvertScalar<T>(ExecuteScalar(transaction, commandType, commandConverted.TrimToLine(), parametersConverted));
        }

        /// <summary>
        /// 执行命令语句，并返回<see cref="T:System.Xml.XmlReader"/>实例对象。
        /// </summary>
        /// <param name="transaction">事务处理实例对象。</param>
        /// <param name="commandType">命令类型。</param>
        /// <param name="commandText">SQL语句。</param>
        /// <param name="parameters">参数数组。</param>
        /// <returns>返回<see cref="T:System.Xml.XmlReader"/>实例对象。</returns>
        public XmlReader ExecuteXmlReader(IDbTransaction transaction, CommandType commandType, string commandText, params IParameter[] parameters)
        {
            string commandConverted = ConvertCommand(commandText);
            TParameter[] parametersConverted = ConvertParameters(parameters);
            return ExecuteXmlReader(transaction, commandType, commandConverted.TrimToLine(), parametersConverted);
        }
        #endregion


        #region pager
        /// <summary>
        /// 分页查询，并返回总数量。
        /// </summary>
        /// <param name="pageIndex">页码，从1开始。</param>
        /// <param name="pageSize">每页显示记录数。</param>
        /// <param name="commandText">SQL语句。</param>
        /// <param name="size">返回总记录数。</param>
        /// <param name="parameters">参数。</param>
        /// <returns>返回数据列表。</returns>
        public virtual List<T> Fetch<T>(int pageIndex, int pageSize, string commandText, out int size, params IParameter[] parameters) where T : class,new()
        {
            if (string.IsNullOrEmpty(commandText) || !pager.IsMatch(commandText))
                throw new Exception("The command text “{0}” is empty or is not correct syntax!".FormatWith(commandText));
            var match = pager.Match(commandText);
            pageIndex = Math.Max(1, pageIndex);
            return ExecuteReader<T>(ApplyPageCommandText(pageIndex, pageSize, match.Groups[1].Value, match.Groups[2].Value, match.Groups[3].Value), parameters, out size);
        }

        /// <summary>
        /// 分页查询，并返回总数量。
        /// </summary>
        /// <param name="commandText">SQL语句。</param>
        /// <param name="size">返回数据总记录数。</param>
        /// <param name="parameters">参数。</param>
        /// <returns>返回数据列表。</returns>
        protected virtual List<T> ExecuteReader<T>(string commandText, IParameter[] parameters, out int size) where T : class,new()
        {
            var list = new List<T>();
            using (var reader = ExecuteReader(commandText, parameters))
            {
                while (reader.Read())
                {
                    list.Add(reader.As<T>());
                }
                size = reader.GetSize();
            }
            return list;
        }

        private static readonly Regex pager = new Regex(@"SELECT(.*?)FROM\s+(.*?)(ORDER BY.*)", RegexOptions.Compiled | RegexOptions.IgnoreCase | RegexOptions.Singleline);
        /// <summary>
        /// 返回分页SQL语句字符串。
        /// </summary>
        /// <param name="pageIndex">页码，从1开始。</param>
        /// <param name="pageSize">每页显示记录数。</param>
        /// <param name="fields">选择列。</param>
        /// <param name="tbWheres">FROM语句后ORDER BY之前的语句。</param>
        /// <param name="orderby">ORDER BY语句。</param>
        /// <returns>返回组合后的分页查询语句。</returns>
        protected abstract string ApplyPageCommandText(int pageIndex, int pageSize, string fields, string tbWheres, string orderby);
        #endregion

        /// <summary>
        /// 执行单值返回方法。
        /// </summary>
        /// <param name="transaction">事务处理实例对象。</param>
        /// <param name="commandType">命令类型。</param>
        /// <param name="commandText">SQL语句。</param>
        /// <param name="parameters">参数数组。</param>
        /// <returns>返回执行后的结果。</returns>
        protected abstract object ExecuteScalar(IDbTransaction transaction, CommandType commandType, string commandText, TParameter[] parameters);

        /// <summary>
        /// 执行不返回值得查询语句。
        /// </summary>
        /// <param name="transaction">事务处理实例对象。</param>
        /// <param name="commandType">命令类型。</param>
        /// <param name="commandText">SQL语句。</param>
        /// <param name="parameters">参数数组。</param>
        /// <returns>
        /// 返回影响的行数。
        /// </returns>
        protected abstract int ExecuteNonQuery(IDbTransaction transaction, CommandType commandType, string commandText, TParameter[] parameters);

        /// <summary>
        /// 执行查询语句。
        /// </summary>
        /// <param name="transaction">事务处理实例对象。</param>
        /// <param name="commandType">命令类型。</param>
        /// <param name="commandText">SQL语句。</param>
        /// <param name="parameters">参数数组。</param>
        /// <returns>
        /// 返回数据库读取对象实例。
        /// </returns>
        protected abstract IDataReader ExecuteReader(IDbTransaction transaction, CommandType commandType, string commandText, TParameter[] parameters);

        /// <summary>
        /// 执行并返回一个XML读取对象实例。
        /// </summary>
        /// <param name="transaction">事务处理实例对象。</param>
        /// <param name="commandType">命令类型。</param>
        /// <param name="commandText">SQL语句。</param>
        /// <param name="parameters">参数数组。</param>
        /// <returns>
        /// 返回XML读取对象实例。
        /// </returns>
        protected virtual XmlReader ExecuteXmlReader(IDbTransaction transaction, CommandType commandType, string commandText, TParameter[] parameters)
        {
            string xmlString = (string)ExecuteScalar(transaction, commandType, commandText, parameters);
            if(xmlString == null)
                throw new ArgumentException("The query didn't return a value.");

            return XmlReader.Create(new StringReader(xmlString));
        }

        /// <summary>
        /// 析构释放对象。
        /// </summary>
        public virtual void Dispose()
        { }
    }
}
